About linking to another workbook or program

Linking is especially useful when it is not practical to keep large worksheet models together in the same workbook.

• Merge data from several workbooks    You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. That way, when the source workbooks are changed, you won't have to manually change the summary workbook.
• Create different views of your data    You can enter all of your data into one or more source workbooks, and then create a report workbook that contains links to only the pertinent data.
• Streamline large, complex models    By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.

Formulas with links to other workbooks are displayed in two ways, depending on whether the source workbook— the one workbook that supplies data to a formula— is open or closed.

When the source is open, the link includes the workbook name in square brackets, followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.

Link
=SUM([Budget.xls]Annual!C10:C25)

When the source is not open, the link includes the entire path.

Link
=SUM('C:\Reports\[Budget.xls]Annual'!C10:C25)

Note  If the name of the other worksheet or workbook contains nonalphabetic characters, you must enclose the name (or the path) within single quotation marks.

Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!), and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xls.

Link
=SUM(Budget!Sales)

When you copy information between Microsoft Excel or any program that supports Object Linking and Embedding (OLE), such as Microsoft Word, you can copy the information as either a linked object or an embedded object.

Embedded object: No connection to the source file.

Linked object: Linked to the source file.

Source file: Updates the linked object.

When to use linked objects    When you want the information in your destination file to be updated when the data in the source file changes, use linked objects.

With a linked object, the original information remains stored in the source file. The destination file displays a representation of the linked information but stores only the location of the original data (and the size if the object is an Excel chart object). The source file must remain available on your computer or network to maintain the link to the original data.

The linked information is updated automatically if you change the original data in the source file. For example, if you select a range of cells in an Excel workbook and then paste the cells as a linked object in a Word document, the information is updated in Word if you change the information in your workbook.

When to use embedded objects    When you don't want to update the copied data when it changes in the source file, use an embedded object. The version of the source is embedded entirely in the workbook.

When someone opens the file on another computer, they can view the embedded object without having access to the original data. Because an embedded object has no links to the source file, the object is not updated if you change the original data. To change an embedded object, double-click the object to open and edit it in the source program. The source program (or another program capable of editing the object) must be installed on your computer. If you copy information as an embedded object, the destination file requires more disk space than if you link the information.

A linked object is updated automatically by default every time you open the destination file or any time the source file changes while the destination file is open. When you open the workbook, a startup prompt appears, asking if you want to update the links. This is the primary way to update links, although you can also manually update them. You can also control the response to the prompt, or whether to display it at all.

If you use a formula to link data from other programs, that data is updated automatically in Microsoft Excel whenever it is changed in the other program.

Security  Dynamic data exchange (DDE) is an older technology that is not secure. If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE).

You can also create a link using Dynamic Data Exchange (DDE) if the program you are linking to supports it. To determine whether your program supports DDE or to learn the DDE name and topic information for the program, contact the software vendor of the program. For more information about DDE, see the Microsoft Office 2003 Resource Kit.